using System;
using System.Data;
using System.Data.OleDb;
using PCSComUtils.Common;
using PCSComUtils.DataAccess;

using PCSComUtils.PCSExc;
using System.Runtime.InteropServices;

namespace PCSComMaterials.Inventory.DS
{
	[Guid("8B82E675-FDD4-4344-814B-E60CEFC24280")]
	public class IV_LotFIFODS ////: IObjectDS
	{
		public IV_LotFIFODS()
		{
		}

		private const string THIS = "PCSComMaterials.Inventory.DS.IV_LotFIFODS";
		//**************************************************************************              
		///    <Description>
		///       This method uses to add data to IV_LotFIFO
		///    </Description>
		///    <Inputs>
		///        IV_LotFIFOVO       
		///    </Inputs>
		///    <Outputs>
		///       newly inserted primarkey value
		///    </Outputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void Add(object pobjObjectVO)
		{
			const string METHOD_NAME = THIS + ".Add()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				IV_LotFIFOVO objObject = (IV_LotFIFOVO) pobjObjectVO;
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand("", oconPCS);

				strSql = "INSERT INTO IV_LotFIFO("
					+ IV_LotFIFOTable.ACTUALCOST21_FLD + ","
					+ IV_LotFIFOTable.COSTUPDATE_FLD + ","
					+ IV_LotFIFOTable.LOT_FLD + ","
					+ IV_LotFIFOTable.LOTSEQUENCE_FLD + ","
					+ IV_LotFIFOTable.RECEIVEDATE_FLD + ","
					+ IV_LotFIFOTable.PRODUCTID_FLD + ","
					+ IV_LotFIFOTable.CCNID_FLD + ")"
					+ "VALUES(?,?,?,?,?,?,?)";

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.ACTUALCOST21_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[IV_LotFIFOTable.ACTUALCOST21_FLD].Value = objObject.ActualCost21;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.COSTUPDATE_FLD, OleDbType.Boolean));
				ocmdPCS.Parameters[IV_LotFIFOTable.COSTUPDATE_FLD].Value = objObject.CostUpdate;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_LotFIFOTable.LOT_FLD].Value = objObject.Lot;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.LOTSEQUENCE_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.LOTSEQUENCE_FLD].Value = objObject.LotSequence;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.RECEIVEDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[IV_LotFIFOTable.RECEIVEDATE_FLD].Value = objObject.ReceiveDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.PRODUCTID_FLD].Value = objObject.ProductID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.CCNID_FLD].Value = objObject.CCNID;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();

			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to add data to IV_LotFIFO
		///    </Description>
		///    <Inputs>
		///        IV_LotFIFOVO       
		///    </Inputs>
		///    <Outputs>
		///       newly inserted primarkey value
		///    </Outputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <Authors>
		///       DungLa
		///    </Authors>
		///    <History>
		///       11-Mar-2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public int AddAndReturnID(object pobjObjectVO)
		{
			const string METHOD_NAME = THIS + ".AddAndReturnID()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				IV_LotFIFOVO objObject = (IV_LotFIFOVO) pobjObjectVO;
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand("", oconPCS);

				strSql = "INSERT INTO " + IV_LotFIFOTable.TABLE_NAME + "("
					+ IV_LotFIFOTable.ACTUALCOST21_FLD + ","
					+ IV_LotFIFOTable.COSTUPDATE_FLD + ","
					+ IV_LotFIFOTable.LOT_FLD + ","
					+ IV_LotFIFOTable.LOTSEQUENCE_FLD + ","
					+ IV_LotFIFOTable.RECEIVEDATE_FLD + ","
					+ IV_LotFIFOTable.PRODUCTID_FLD + ","
					+ IV_LotFIFOTable.CCNID_FLD + ")"
					+ "VALUES(?,?,?,?,?,?,?)";
				strSql += " ; SELECT @@IDENTITY AS NEWID";

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.ACTUALCOST21_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[IV_LotFIFOTable.ACTUALCOST21_FLD].Value = objObject.ActualCost21;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.COSTUPDATE_FLD, OleDbType.Boolean));
				ocmdPCS.Parameters[IV_LotFIFOTable.COSTUPDATE_FLD].Value = objObject.CostUpdate;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_LotFIFOTable.LOT_FLD].Value = objObject.Lot;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.LOTSEQUENCE_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.LOTSEQUENCE_FLD].Value = objObject.LotSequence;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.RECEIVEDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[IV_LotFIFOTable.RECEIVEDATE_FLD].Value = objObject.ReceiveDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.PRODUCTID_FLD].Value = objObject.ProductID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.CCNID_FLD].Value = objObject.CCNID;

				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				return int.Parse(ocmdPCS.ExecuteScalar().ToString());
				//ocmdPCS.ExecuteNonQuery();
			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to add data to IV_LotFIFO
		///    </Description>
		///    <Inputs>
		///        IV_LotFIFOVO       
		///    </Inputs>
		///    <Outputs>
		///       newly inserted primarkey value
		///    </Outputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void AddReturnedGoods(object pobjObjectVO)
		{
			const string METHOD_NAME = THIS + ".Add()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				IV_LotFIFOVO objObject = (IV_LotFIFOVO) pobjObjectVO;
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand("", oconPCS);

				strSql = "SELECT isnull(Max("
					+ IV_LotFIFOTable.LOTSEQUENCE_FLD + "),0) "
					+ " FROM " + IV_LotFIFOTable.TABLE_NAME;

				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				int intLotSequence = int.Parse(ocmdPCS.ExecuteScalar().ToString()) + 1;

				//first get the max sequence for this

				strSql = "INSERT INTO IV_LotFIFO("
					+ IV_LotFIFOTable.ACTUALCOST21_FLD + ","
					+ IV_LotFIFOTable.LOT_FLD + ","
					+ IV_LotFIFOTable.LOTSEQUENCE_FLD + ","
					+ IV_LotFIFOTable.RECEIVEDATE_FLD + ","
					+ IV_LotFIFOTable.PRODUCTID_FLD + ","
					+ IV_LotFIFOTable.CCNID_FLD + ")"
					+ "VALUES(?,?,?,?,?,?)";

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.ACTUALCOST21_FLD, OleDbType.Decimal));
				if (objObject.ActualCost21 >= 0)
				{
					ocmdPCS.Parameters[IV_LotFIFOTable.ACTUALCOST21_FLD].Value = objObject.ActualCost21;
				}
				else
				{
					ocmdPCS.Parameters[IV_LotFIFOTable.ACTUALCOST21_FLD].Value = DBNull.Value;
				}

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_LotFIFOTable.LOT_FLD].Value = objObject.Lot;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.LOTSEQUENCE_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.LOTSEQUENCE_FLD].Value = intLotSequence;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.RECEIVEDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[IV_LotFIFOTable.RECEIVEDATE_FLD].Value = objObject.ReceiveDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.PRODUCTID_FLD].Value = objObject.ProductID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.CCNID_FLD].Value = objObject.CCNID;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.ExecuteNonQuery();

			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to delete data from IV_LotFIFO
		///    </Description>
		///    <Inputs>
		///        ID       
		///    </Inputs>
		///    <Outputs>
		///       void
		///    </Outputs>
		///    <Returns>
		///       
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       09-Dec-2004
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void Delete(int pintID)
		{
			const string METHOD_NAME = THIS + ".Delete()";
			string strSql = String.Empty;
			strSql = "DELETE " + IV_LotFIFOTable.TABLE_NAME + " WHERE  " + "LotFIFOID" + "=" + pintID.ToString();
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
				ocmdPCS = null;

			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}


			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to get data from IV_LotFIFO
		///    </Description>
		///    <Inputs>
		///        ID       
		///    </Inputs>
		///    <Outputs>
		///       IV_LotFIFOVO
		///    </Outputs>
		///    <Returns>
		///       IV_LotFIFOVO
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public object GetObjectVO(int pintID)
		{
			const string METHOD_NAME = THIS + ".GetObjectVO()";
			
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "SELECT "
					+ IV_LotFIFOTable.LOTFIFOID_FLD + ","
					+ IV_LotFIFOTable.ACTUALCOST21_FLD + ","
					+ IV_LotFIFOTable.COSTUPDATE_FLD + ","
					+ IV_LotFIFOTable.LOT_FLD + ","
					+ IV_LotFIFOTable.LOTSEQUENCE_FLD + ","
					+ IV_LotFIFOTable.RECEIVEDATE_FLD + ","
					+ IV_LotFIFOTable.PRODUCTID_FLD + ","
					+ IV_LotFIFOTable.CCNID_FLD
					+ " FROM " + IV_LotFIFOTable.TABLE_NAME
					+ " WHERE " + IV_LotFIFOTable.LOTFIFOID_FLD + "=" + pintID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				IV_LotFIFOVO objObject = new IV_LotFIFOVO();

				while (odrPCS.Read())
				{
					objObject.LotFIFOID = int.Parse(odrPCS[IV_LotFIFOTable.LOTFIFOID_FLD].ToString().Trim());
					objObject.ActualCost21 = Decimal.Parse(odrPCS[IV_LotFIFOTable.ACTUALCOST21_FLD].ToString().Trim());
					objObject.CostUpdate = bool.Parse(odrPCS[IV_LotFIFOTable.COSTUPDATE_FLD].ToString().Trim());
					objObject.Lot = odrPCS[IV_LotFIFOTable.LOT_FLD].ToString().Trim();
					objObject.LotSequence = int.Parse(odrPCS[IV_LotFIFOTable.LOTSEQUENCE_FLD].ToString().Trim());
					objObject.ReceiveDate = DateTime.Parse(odrPCS[IV_LotFIFOTable.RECEIVEDATE_FLD].ToString().Trim());
					objObject.ProductID = int.Parse(odrPCS[IV_LotFIFOTable.PRODUCTID_FLD].ToString().Trim());
					objObject.CCNID = int.Parse(odrPCS[IV_LotFIFOTable.CCNID_FLD].ToString().Trim());

				}
				return objObject;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to get data from IV_LotFIFO
		///    </Description>
		///    <Inputs>
		///        ID       
		///    </Inputs>
		///    <Outputs>
		///       IV_LotFIFOVO
		///    </Outputs>
		///    <Returns>
		///       IV_LotFIFOVO
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public decimal GetActualCost21(int pintProductID, int pintCCNID, string strLot)
		{
			const string METHOD_NAME = THIS + ".GetActualCost21()";
			
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "SELECT "
					+ IV_LotFIFOTable.ACTUALCOST21_FLD 
					+ " FROM " + IV_LotFIFOTable.TABLE_NAME
					+ " WHERE " + IV_LotFIFOTable.PRODUCTID_FLD + "=?" 
					+ " AND " + IV_LotFIFOTable.CCNID_FLD + "=?" 
					+ " AND " + IV_LotFIFOTable.LOT_FLD + "=?" ;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);


				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.PRODUCTID_FLD].Value = pintProductID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.CCNID_FLD].Value = pintCCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_LotFIFOTable.LOT_FLD].Value = strLot;

				ocmdPCS.Connection.Open();
				object objResult = ocmdPCS.ExecuteScalar();
				if (objResult == null)
				{
					return -1;
				}
				else
				{
					return decimal.Parse(objResult.ToString());
				}

			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to update data to IV_LotFIFO
		///    </Description>
		///    <Inputs>
		///       IV_LotFIFOVO       
		///    </Inputs>
		///    <Outputs>
		///       
		///    </Outputs>
		///    <Returns>
		///       
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       09-Dec-2004
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void Update(object pobjObjecVO)
		{
			const string METHOD_NAME = THIS + ".Update()";

			IV_LotFIFOVO objObject = (IV_LotFIFOVO) pobjObjecVO;


			//prepare value for parameters
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql = "UPDATE IV_LotFIFO SET "
					+ IV_LotFIFOTable.ACTUALCOST21_FLD + "=   ?" + ","
					+ IV_LotFIFOTable.COSTUPDATE_FLD + "=   ?" + ","
					+ IV_LotFIFOTable.LOT_FLD + "=   ?" + ","
					+ IV_LotFIFOTable.LOTSEQUENCE_FLD + "=   ?" + ","
					+ IV_LotFIFOTable.RECEIVEDATE_FLD + "=   ?" + ","
					+ IV_LotFIFOTable.PRODUCTID_FLD + "=   ?" + ","
					+ IV_LotFIFOTable.CCNID_FLD + "=  ?"
					+ " WHERE " + IV_LotFIFOTable.LOTFIFOID_FLD + "= ?";

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.ACTUALCOST21_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[IV_LotFIFOTable.ACTUALCOST21_FLD].Value = objObject.ActualCost21;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.COSTUPDATE_FLD, OleDbType.Boolean));
				ocmdPCS.Parameters[IV_LotFIFOTable.COSTUPDATE_FLD].Value = objObject.CostUpdate;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_LotFIFOTable.LOT_FLD].Value = objObject.Lot;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.LOTSEQUENCE_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.LOTSEQUENCE_FLD].Value = objObject.LotSequence;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.RECEIVEDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[IV_LotFIFOTable.RECEIVEDATE_FLD].Value = objObject.ReceiveDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.PRODUCTID_FLD].Value = objObject.ProductID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.CCNID_FLD].Value = objObject.CCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.LOTFIFOID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.LOTFIFOID_FLD].Value = objObject.LotFIFOID;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}

		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to update data to IV_LotFIFO
		///    </Description>
		///    <Inputs>
		///       IV_LotFIFOVO       
		///    </Inputs>
		///    <Outputs>
		///       
		///    </Outputs>
		///    <Returns>
		///       
		///    </Returns>
		///    <Authors>
		///       DungLa
		///    </Authors>
		///    <History>
		///       31-Mar-2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void UpdateLotFIFO(object pobjObjecVO)
		{
			const string METHOD_NAME = THIS + ".UpdateLotFIFO()";

			IV_LotFIFOVO objObject = (IV_LotFIFOVO) pobjObjecVO;


			//prepare value for parameters
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql = "UPDATE IV_LotFIFO SET "
					+ IV_LotFIFOTable.ACTUALCOST21_FLD + "=   ?" + ","
					+ IV_LotFIFOTable.RECEIVEDATE_FLD + "=   ?"
					+ " WHERE " + IV_LotFIFOTable.CCNID_FLD + "= ?"
					+ " AND " + IV_LotFIFOTable.PRODUCTID_FLD + "= ?"
					+ " AND " + IV_LotFIFOTable.LOT_FLD + "= ?";

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.ACTUALCOST21_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[IV_LotFIFOTable.ACTUALCOST21_FLD].Value = objObject.ActualCost21;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.RECEIVEDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[IV_LotFIFOTable.RECEIVEDATE_FLD].Value = objObject.ReceiveDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.CCNID_FLD].Value = objObject.CCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.PRODUCTID_FLD].Value = objObject.ProductID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_LotFIFOTable.LOT_FLD].Value = objObject.Lot;

				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
			}
			catch (OleDbException ex)
			{
				if (ex.Errors.Count > 0)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
					{
						throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
					}
					else
					{
						throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
					}
				}
				else
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}

		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to get all data from IV_LotFIFO
		///    </Description>
		///    <Inputs>
		///               
		///    </Inputs>
		///    <Outputs>
		///       DataSet
		///    </Outputs>
		///    <Returns>
		///       DataSet
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public DataSet List()
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();


			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;

				strSql = "SELECT "
					+ IV_LotFIFOTable.LOTFIFOID_FLD + ","
					+ IV_LotFIFOTable.ACTUALCOST21_FLD + ","
					+ IV_LotFIFOTable.COSTUPDATE_FLD + ","
					+ IV_LotFIFOTable.LOT_FLD + ","
					+ IV_LotFIFOTable.LOTSEQUENCE_FLD + ","
					+ IV_LotFIFOTable.RECEIVEDATE_FLD + ","
					+ IV_LotFIFOTable.PRODUCTID_FLD + ","
					+ IV_LotFIFOTable.CCNID_FLD
					+ " FROM " + IV_LotFIFOTable.TABLE_NAME;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, IV_LotFIFOTable.TABLE_NAME);

				return dstPCS;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}


		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to update a DataSet
		///    </Description>
		///    <Inputs>
		///        DataSet       
		///    </Inputs>
		///    <Outputs>
		///       
		///    </Outputs>
		///    <Returns>
		///       
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void UpdateDataSet(DataSet pData)
		{
			const string METHOD_NAME = THIS + ".UpdateDataSet()";
			string strSql;
			OleDbConnection oconPCS = null;
			OleDbCommandBuilder odcbPCS;
			OleDbDataAdapter odadPCS = new OleDbDataAdapter();

			try
			{
				strSql = "SELECT "
					+ IV_LotFIFOTable.LOTFIFOID_FLD + ","
					+ IV_LotFIFOTable.ACTUALCOST21_FLD + ","
					+ IV_LotFIFOTable.COSTUPDATE_FLD + ","
					+ IV_LotFIFOTable.LOT_FLD + ","
					+ IV_LotFIFOTable.LOTSEQUENCE_FLD + ","
					+ IV_LotFIFOTable.RECEIVEDATE_FLD + ","
					+ IV_LotFIFOTable.PRODUCTID_FLD + ","
					+ IV_LotFIFOTable.CCNID_FLD
					+ "  FROM " + IV_LotFIFOTable.TABLE_NAME;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				odadPCS.SelectCommand = new OleDbCommand(strSql, oconPCS);
				odcbPCS = new OleDbCommandBuilder(odadPCS);
				pData.EnforceConstraints = false;
				odadPCS.Update(pData, IV_LotFIFOTable.TABLE_NAME);

			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
				}

				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}

		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to get max sequence of MST_TransactionHistory
		///    </Description>
		///    <Inputs>
		///        
		///    </Inputs>
		///    <Outputs>
		///       int
		///    </Outputs>
		///    <Returns>
		///       int
		///    </Returns>
		///    <Authors>
		///       DungLa
		///    </Authors>
		///    <History>
		///       11-Mar-2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public int GetMaxSequence()
		{
			const string METHOD_NAME = THIS + ".GetMaxSequence()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "SELECT ISNULL(MAX("
					+ IV_LotFIFOTable.LOTSEQUENCE_FLD + "), 0)"
					+ " FROM " + IV_LotFIFOTable.TABLE_NAME;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				object objResult = ocmdPCS.ExecuteScalar();
				if (objResult != null)
					return (int.Parse(objResult.ToString()));
				else
					return 0;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}
		//**************************************************************************              
		///    <Description>
		///       This method uses to check for existing product of Lot
		///    </Description>
		///    <Inputs>
		///        Lot, CCNID, ProductID
		///    </Inputs>
		///    <Outputs>
		///       IV_LotItemVO
		///    </Outputs>
		///    <Returns>
		///       IV_LotItemVO
		///    </Returns>
		///    <Authors>
		///       DungLa
		///    </Authors>
		///    <History>
		///       31-Mar-2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public bool HasProduct(string pstrLot, int pintCCNID, int pintProductID)
		{
			const string METHOD_NAME = THIS + ".HasProduct()";
			
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "SELECT ISNULL(COUNT(*),0) "
					+ " FROM " + IV_LotFIFOTable.TABLE_NAME
					+ " WHERE " + IV_LotFIFOTable.LOT_FLD + "= ?"
					+ " AND " + IV_LotFIFOTable.CCNID_FLD + "= ?"
					+ " AND " + IV_LotFIFOTable.PRODUCTID_FLD + "= ?";

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_LotFIFOTable.LOT_FLD].Value = pstrLot;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.CCNID_FLD].Value = pintCCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotFIFOTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotFIFOTable.PRODUCTID_FLD].Value = pintProductID;


				ocmdPCS.Connection.Open();
				object objResult = ocmdPCS.ExecuteScalar();
				if (objResult == null)
				{
					return false;
				}
				else
				{
					if (int.Parse(objResult.ToString()) > 0)
					{
						return true;
					}
					else
					{
						return false;
					}
				}

			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		//**************************************************************************              
		///    <Description>
		///    Get ActualCost21 Cost by ProductID and CCNID
		///    </Description>
		///    <Inputs>
		///    </Inputs>
		///    <Outputs>
		///    </Outputs>
		///    <Returns>
		///       Decimal
		///    </Returns>
		///    <Authors>
		///       TuanDm
		///    </Authors>
		///    <History>
		///       Monday, May 09, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public decimal GetActualCost21ProductIDCCNID(int pintProductID, int pintCCNID)
		{
			const string METHOD_NAME = THIS + ".GetAVGCostByProductIDCCNID()";
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = "SELECT "
					+ IV_LotFIFOTable.ACTUALCOST21_FLD
					+ " FROM " + IV_LotFIFOTable.TABLE_NAME
					+ " WHERE " + IV_LotFIFOTable.CCNID_FLD + "=" + pintCCNID + " and " + IV_LotFIFOTable.PRODUCTID_FLD + " = " + pintProductID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				object objResult = ocmdPCS.ExecuteScalar();
				if (objResult == null)
				{
					return 0;
				}
				else
				{
					return decimal.Parse(objResult.ToString());
				}
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
	}
}